if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_Milestone_Project]') AND parent_object_id = OBJECT_ID('pm_Milestone'))
alter table pm_Milestone  drop constraint FK_Milestone_Project

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_LogEntry_WorkItem]') AND parent_object_id = OBJECT_ID('pm_LogEntry'))
alter table pm_LogEntry  drop constraint FK_LogEntry_WorkItem

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_LogEntry_Person]') AND parent_object_id = OBJECT_ID('pm_LogEntry'))
alter table pm_LogEntry  drop constraint FK_LogEntry_Person

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_ProjectPerson_Project]') AND parent_object_id = OBJECT_ID('pm_ProjectPerson'))
alter table pm_ProjectPerson  drop constraint FK_ProjectPerson_Project

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_ProjectPerson_Person]') AND parent_object_id = OBJECT_ID('pm_ProjectPerson'))
alter table pm_ProjectPerson  drop constraint FK_ProjectPerson_Person

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_WorkItem_Project]') AND parent_object_id = OBJECT_ID('pm_WorkItem'))
alter table pm_WorkItem  drop constraint FK_WorkItem_Project

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_WorkItem_ReportedByPerson]') AND parent_object_id = OBJECT_ID('pm_WorkItem'))
alter table pm_WorkItem  drop constraint FK_WorkItem_ReportedByPerson

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_WorkItem_AssignedToPerson]') AND parent_object_id = OBJECT_ID('pm_WorkItem'))
alter table pm_WorkItem  drop constraint FK_WorkItem_AssignedToPerson

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_WorkItem_WorkItemStatus]') AND parent_object_id = OBJECT_ID('pm_WorkItem'))
alter table pm_WorkItem  drop constraint FK_WorkItem_WorkItemStatus

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_WorkItem_Category]') AND parent_object_id = OBJECT_ID('pm_WorkItem'))
alter table pm_WorkItem  drop constraint FK_WorkItem_Category

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK_WorkItem_Milestone]') AND parent_object_id = OBJECT_ID('pm_WorkItem'))
alter table pm_WorkItem  drop constraint FK_WorkItem_Milestone

if exists (select * from dbo.sysobjects where id = object_id(N'pm_Milestone') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table pm_Milestone
if exists (select * from dbo.sysobjects where id = object_id(N'pm_WorkItemStatus') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table pm_WorkItemStatus
if exists (select * from dbo.sysobjects where id = object_id(N'pm_LogEntry') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table pm_LogEntry
if exists (select * from dbo.sysobjects where id = object_id(N'pm_Person') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table pm_Person
if exists (select * from dbo.sysobjects where id = object_id(N'pm_WorkItemCategory') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table pm_WorkItemCategory
if exists (select * from dbo.sysobjects where id = object_id(N'pm_ProjectPerson') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table pm_ProjectPerson
if exists (select * from dbo.sysobjects where id = object_id(N'pm_WorkItem') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table pm_WorkItem
if exists (select * from dbo.sysobjects where id = object_id(N'pm_Project') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table pm_Project
create table pm_Milestone (
  Id INT IDENTITY NOT NULL,
   ConcurrencyDate DATETIME not null,
   AddedBy VARCHAR(100) not null,
   AddedDate DATETIME not null,
   UpdatedBy VARCHAR(100) null,
   UpdatedDate DATETIME null,
   ProjectId INT not null,
   Name VARCHAR(100) not null,
   primary key (Id),
  unique (ProjectId, Name)
)
create table pm_WorkItemStatus (
  Id INT IDENTITY NOT NULL,
   ConcurrencyDate DATETIME not null,
   AddedBy VARCHAR(100) not null,
   AddedDate DATETIME not null,
   UpdatedBy VARCHAR(100) null,
   UpdatedDate DATETIME null,
   Description VARCHAR(100) not null unique,
   IsOpen BIT not null,
   primary key (Id)
)
create table pm_LogEntry (
  Id INT IDENTITY NOT NULL,
   ConcurrencyDate DATETIME not null,
   AddedBy VARCHAR(100) not null,
   AddedDate DATETIME not null,
   UpdatedBy VARCHAR(100) null,
   UpdatedDate DATETIME null,
   WorkItemId INT not null,
   PersonId INT not null,
   LogEntryType INT not null,
   Detail VARCHAR(MAX) not null,
   primary key (Id)
)
create table pm_Person (
  Id INT IDENTITY NOT NULL,
   ConcurrencyDate DATETIME not null,
   AddedBy VARCHAR(100) not null,
   AddedDate DATETIME not null,
   UpdatedBy VARCHAR(100) null,
   UpdatedDate DATETIME null,
   PortalId INT not null,
   Username VARCHAR(100) not null,
   DisplayName VARCHAR(100) not null,
   primary key (Id),
  unique (PortalId, Username)
)
create table pm_WorkItemCategory (
  Id INT IDENTITY NOT NULL,
   ConcurrencyDate DATETIME not null,
   AddedBy VARCHAR(100) not null,
   AddedDate DATETIME not null,
   UpdatedBy VARCHAR(100) null,
   UpdatedDate DATETIME null,
   Description VARCHAR(100) not null unique,
   primary key (Id)
)
create table pm_ProjectPerson (
  Id INT IDENTITY NOT NULL,
   ConcurrencyDate DATETIME not null,
   AddedBy VARCHAR(100) not null,
   AddedDate DATETIME not null,
   UpdatedBy VARCHAR(100) null,
   UpdatedDate DATETIME null,
   ProjectId INT not null,
   PersonId INT not null,
   Role INT not null,
   primary key (Id),
  unique (ProjectId, PersonId)
)
create table pm_WorkItem (
  Id INT IDENTITY NOT NULL,
   ConcurrencyDate DATETIME not null,
   AddedBy VARCHAR(100) not null,
   AddedDate DATETIME not null,
   UpdatedBy VARCHAR(100) null,
   UpdatedDate DATETIME null,
   ProjectId INT not null,
   Summary VARCHAR(200) not null,
   Description VARCHAR(MAX) not null,
   ReportedByPersonId INT not null,
   AssignedToPersonId INT null,
   WorkItemStatusId INT not null,
   WorkItemCategoryId INT not null,
   MilestoneId INT not null,
   Priority INT not null,
   primary key (Id),
  unique (ProjectId, Summary)
)
create table pm_Project (
  Id INT IDENTITY NOT NULL,
   ConcurrencyDate DATETIME not null,
   AddedBy VARCHAR(100) not null,
   AddedDate DATETIME not null,
   UpdatedBy VARCHAR(100) null,
   UpdatedDate DATETIME null,
   Name VARCHAR(100) not null,
   PortalId INT not null,
   primary key (Id)
)
alter table pm_Milestone add constraint FK_Milestone_Project foreign key (ProjectId) references pm_Project
alter table pm_LogEntry add constraint FK_LogEntry_WorkItem foreign key (WorkItemId) references pm_WorkItem
alter table pm_LogEntry add constraint FK_LogEntry_Person foreign key (PersonId) references pm_Person
alter table pm_ProjectPerson add constraint FK_ProjectPerson_Project foreign key (ProjectId) references pm_Project
alter table pm_ProjectPerson add constraint FK_ProjectPerson_Person foreign key (PersonId) references pm_Person
alter table pm_WorkItem add constraint FK_WorkItem_Project foreign key (ProjectId) references pm_Project
alter table pm_WorkItem add constraint FK_WorkItem_ReportedByPerson foreign key (ReportedByPersonId) references pm_Person
alter table pm_WorkItem add constraint FK_WorkItem_AssignedToPerson foreign key (AssignedToPersonId) references pm_Person
alter table pm_WorkItem add constraint FK_WorkItem_WorkItemStatus foreign key (WorkItemStatusId) references pm_WorkItemStatus
alter table pm_WorkItem add constraint FK_WorkItem_Category foreign key (WorkItemCategoryId) references pm_WorkItemCategory
alter table pm_WorkItem add constraint FK_WorkItem_Milestone foreign key (MilestoneId) references pm_Milestone
